import pymysql
import uuid
import random


#数据库连接
mysql_cfg = {
            'host': 'localhost',
            'user': 'root',
            'password': 'test1234',
            'database': 'tianya'
        }
db_connect = pymysql.connect(**mysql_cfg)
db_cursor = db_connect.cursor()

#店铺订单函数
def my_order_generator():
    creat_sql='''
            create table my_order(
            id int primary key auto_increment,
            order_id varchar(255),
            store_name varchar(255),
            amount decimal(10, 2),
            creat_time datetime default current_timestamp
            );
    '''
    #db_cursor.execute(creat_sql)#创建my_order表格
    SQL =  """insert into my_order(order_id, store_name, amount) values (%s, %s, %s)"""#为my_order表格中添加字段
    #args = [(str(uuid.uuid1()), f"store_{random.randint(0, 18)}", format(random.random() * 1000, ".2f"))
    args = [(str(uuid.uuid1()), None, None)
            for i in range(0, 10)]#数据参数列表
    db_cursor.executemany(SQL, args)#向表格中添加数据记录
    db_cursor.close()
    db_connect.commit()
    db_connect.close()


#事业部—店铺函数
def depart_store_generator():
    creat_sql='''
            create table depart_store(
            id int primary key auto_increment,
            store_name varchar(255),
            department varchar(255)
            );
    '''
    db_cursor.execute(creat_sql)#建立depart_store表格
    store_array = [f"store_{i}" for i in range(15)]#创建store0-store14的店铺列表
    random.shuffle(store_array)#洗牌store_array顺序
    print(store_array)
    SQL ='''insert into depart_store(department,store_name)values(%s,%s)'''
    for i in range(3):#间距3选取5个店铺
        store_array_tmp = store_array[i::3]
        args = [(f"depart_{i}", f"{store_name}")for store_name in store_array_tmp]
        db_cursor.executemany(SQL, args)
    db_cursor.close()
    db_connect.commit()
    db_connect.close()


if __name__ == "__main__":
    my_order_generator()
    #depart_store_generator()


'''
1、现在有一批订单数据, 表结构如下, 请将订单数据汇总后算出店铺业绩.
id	order_id	store_name	amount	create_time
*******************************************
select store_name, sum(amount) as total from my_order group by store_name


2、现在有一张事业部和店铺的关系表, 请在订单表my_order的基础上汇总事业部的业绩.
id  	store_name	 department
*************************************************
select t2.department, sum(total) as p_total from
(select store_name, sum(amount) as total from my_order group by store_name) as t1
join 
(select store_name, department from depart_store) as t2
on t1.store_name = t2.store_name
group by t2.department;


3、现在公司规定各个部门业绩都必须达到1w6才算完成业绩目标, 我们需要统计一下达标率
select sum(case when t.p_total >1600 then 1 else 0 end)/count(*) as 达标率
from
(
select t2.department, sum(total) as p_total from
(select store_name, sum(amount) as total from my_order group by store_name) as t1
left join 
(select store_name, department from depart_store) as t2
on t1.store_name = t2.store_name
where t1.store_name is not null and t2.store_name is not null 
group by t2.department
)t

4、现在事业部收到公司下达的达标指标, 然后又各自下达了店铺的达标指标, 需要每天统计订单数, 业绩总额, 达标率.
4.1 底表
select * from 
depart_store as t1
join
my_order as t2
on t1.store_name = t2.store_name
4.2如果店铺的达标阈值时600, 统计各个部门下的订单数, 总业绩, 店铺达标率
select department
,count(*) as order_count
,sum(amount) as total_amount
,sum(case when amount>600 then 1 else 0 end)/count(*) as 达标率
from
(
depart_store as t1
join
my_order as t2
on t1.store_name = t2.store_name
) 
group by department
4.3统计各个店铺下单个订单金额超过600的总数和比率
select t1.store_name
,count(*) as order_count
,sum(amount) as total_amount
,sum(case when amount>600 then 1 else 0 end)/count(*) as 达标率
from
(
depart_store as t1
join
my_order as t2
on t1.store_name = t2.store_name
) 
group by t1.store_name
4.4如果店铺和部门是一对多的关系
select t1.department
,t1.store_name
,count(*) as order_count
,sum(amount) as total_amount
,sum(case when amount>600 then 1 else 0 end)/count(*) as 达标率
from
(
depart_store as t1
join
my_order as t2
on t1.store_name = t2.store_name
) 
group by t1.store_name,t1.department
5、统计事业部的业绩数据, 并筛选出未达标的事业部数据
一般做法：
select * from
(
select department
,count(*) as order_count
,sum(amount) as total_amount
,sum(case when amount>600 then 1 else 0 end)/count(*) as 达标率
from
(
depart_store as t1
join
my_order as t2
on t1.store_name = t2.store_name
) 
group by department
) t
where t.total_amount <1600  #筛选出未达标的事业部数据，总额<1600为未达标

优化做法：
select department
,count(*) as order_count
,sum(amount) as total_amount
,sum(case when amount>600 then 1 else 0 end)/count(*) as 达标率
from
(
depart_store as t1
join
my_order as t2
on t1.store_name = t2.store_name
) 
group by department
having sum(amount) < 1600 #筛选出未达标的事业部数据，总额<1600为未达标
'''

